Oracle Performance Tuning.html
Oracle Performance Tuning, the topic is not that scary as it
sounds. Actually it's very simple if you understand the
performance tuning concept -the bigger picture or in other
words bird's eye view.
Performance tuning is nothing but the collection of statistics
regarding the current status of the system and then change
system values based on the statistics to enhance the
performance. In other words performance tuning is
. Collection of system statistics
. Changing system based on the statistics
Now we have to look into what are different sources that can
provide us the information regarding the current health of the
system. Although there are a lot of sources. The simple
sources, which can provide us the Oracle system information,
are alert log file, trace files, dynamic performance view, data
dictionary views etc. Let's start learning about each of these
sources one by one.
Alert Log File
The location of Alert log file is described by
BACKGROUND_DUMP_DEST initialization parameter. For
Oracle systems that follow OFA (Oracle Flexible Architecture)
or in other words standard directory architecture of Oracle
files, the location of Alter Log file will be as follows.
In Windows Server 2003/NT/XP,
%ORACLE_BASE%\admin\SID\bdump whereas in Unix it
would be $ORACLE_BASE/admin/SID/bdump. Where SID is
the name of the Oracle Instance. You might have noticed that
in Unix environment we use “forward slash” (/) whereas in
Windows we user “backward slash” (\).
Oracle writes all the error messages and other alerts to this file
whenever that message occurs during it normal operations.
Background Process, Event & User Trace Files
Oracle trace files contain information pertaining to certain
event that happens in the regular operation of Oracle. The
location of these trace files is described by
BACKGROUND_DUMP_DEST initialization parameter. For
Oracle systems that follow OFA (Oracle Flexible Architecture)
or in other words standard directory architecture of Oracle
files, the location of these trace files will be as follows.
In Windows Server 2003/NT/XP,
%ORACLE_BASE%\admin\SID\bdump whereas in Unix it
would be $ORACLE_BASE/admin/SID/bdump. Where SID is
the name of the Oracle Instance.
All trace files have file extension of .trc no matter whether it is
Background process, Event or User trace file. In Windows
environment, the background processes will generate the
following files.
Process Monitor (PMON): sidPMON.trc
System Monitor (SMON): sidSMON.trc
Database Writer (DBW0): sidDBW0.trc
Archive Process (ARC0): sidARC0.trc
Log Writer (LGWR): sidLGWR.tc
If we have two Database Writer processes (DBW0, DBW1)
then you may see one more trace file sidDBW1.trc besides
sidDBWR0.trc, this applies to Archiver Process too. Similarly
for Checkpoint Process (CKPT) it is sidCKPT.trc whereas in
Unix environment the trace files pertaining to each of these
processes would include the name of that process in the file
names e.g. for PMON the trace file name would be
PMON_nnnn.trc etc.
Remember that user trace file will get generated when there is
an error in user's server process which contains the details
regarding that error. We can limit the size of User trace file by
specifying the MAX_DUMP_FILE_SIZE init.ora parameter
e.g. specifying the value of this parameter as follows in init.ora
file will the user trace file size to 20MB.
MAX_DUMP_FILE_SIZE=20M
Dynamic Performance Views (V$)& Data Dictionary Views
Dynamic Performance Views or in other words all the views
that start with V$ and Data Dictionary View are views created
on Oracle base tables. These tables are Oracle Internal tables
and are utilized by Oracle. They reside in the SYSTEM
tablespace and if you remember SYS is the owner of these
tables as these tables get created when we logged in to Oracle
as SYS and we ran two scripts which resulted in these Oracle
base tables and views.
Dynamic Performance Views as the name says are dynamic in
nature i.e. data inside these tables keeps changing and it
depends on the current state of Oracle system. Some of the
very important Dynamic Performance Views along with the
brief description of the stuff they contain are provided below.
V$SGASTAT
Contains information about the size of SGA (System Global
Area) and each of its major components.
V$SESSTAT
Contains the statistics information regarding the each
connected session or in other words connected user.
V$SESSION
Contains the current connection information of each session or
in other words each connected user.
Whereas Data Dictionary Views are static in nature and the
values inside these tables change only when you change the
structure of the database. Some of the very important Data
Dictionary Views along with the brief description of the stuff
they contain are provided below.
DBA_TABLES
Contains information about the tables within the database, their
row and block information.
DBA_DATA_FILES
Contains the information about all the data files within the
database, their names, size etc.